I have two tables on ein staging and one in another DB in the same instance. I am moving data from the staging to the That another database. The staging table is made while moving data from staging. Instead of moving data directly from the excel to the destination i moved it to the staging db as while picking data with SSIS from Excel was giving errors with the column width in the destination as the width picked from excel was more even for one character. Structure of staging table is : CREATE TABLE [dbo].[RISKFACTOR_REFERENCE_TEST]( [RISKFACTORID] [varchar](10) NULL, [STATUS] [varchar](10) NULL, [AGECATEGORYID] [varchar](10) NULL, [PRIORITY] [varchar](10) NULL, [HIGHRISK] [varchar](10) NULL, [DESCRIPTION] [varchar](300) NULL, [ASSIGNMENTMETHOD] [varchar](10) NULL, [DFPPRIORITY] [varchar](10) NULL, [FOODPACKAGEID] [varchar](30) NULL, [DEFAULTASSESSMENT] [varchar](4000) NULL, [DEFAULTPLAN] [varchar](4000) NULL, [EFFECTIVEDATE] [datetime] NULL, [EXPIRATIONDATE] [datetime] NULL, [CREATEUSERID] [varchar](20) NULL, [CREATEDTTM] [datetime] NULL, [MODIFYUSERID] [varchar](20) NULL, [MODIFYDTTM] [datetime] NULL, [THRESHOLDID] [varchar](30) NULL, [AMOUNTBREASTFEEDING] [varchar](10) NULL, [ASSIGNMENTRESTRICTION] [varchar](10) NULL ) ON [PRIMARY] Structure of same table as above on the destination table is: CREATE TABLE [dbo].[RISKFACTORREFERENCE]( [RISKFACTORID] [varchar](6) NOT NULL, [STATUS] [char](1) NOT NULL, [AGECATEGORYID] [char](1) NOT NULL, [PRIORITY] [char](1) NULL, [HIGHRISK] [char](1) NOT NULL, [DESCRIPTION] [varchar](60) NULL, [ASSIGNMENTMETHOD] [char](1) NOT NULL, [DFPPRIORITY] [char](1) NULL, [FOODPACKAGEID] [numeric](10, 0) NULL, [DEFAULTASSESSMENT] [varchar](4000) NULL, [DEFAULTPLAN] [varchar](4000) NULL, [EFFECTIVEDATE] [datetime] NULL, [EXPIRATIONDATE] [datetime] NULL, [CREATEUSERID] [varchar](20) NULL, [CREATEDTTM] [datetime] NULL, [MODIFYUSERID] [varchar](20) NULL, [MODIFYDTTM] [datetime] NULL, [THRESHOLDID] [numeric](18, 0) NULL, [AMOUNTBREASTFEEDING] [char](1) NOT NULL, [ASSIGNMENTRESTRICTION] [char](1) NOT NULL, CONSTRAINT [PK_RISKFACTORREFERENCE] PRIMARY KEY CLUSTERED ( and the query which i am using in the SSIS package is at the OLEDB source that is staging table is: select substring(ltrim(rtrim([RISKFACTORID])),1,6) as RISKFACTORID, -- [varchar](6) NOT NULL, CAST(substring(ltrim(rtrim([STATUS])),1,1) as char(1)) as WICSTATUS, --[char](1) NOT NULL, CAST(substring(ltrim(rtrim([AGECATEGORYID])),1,1) as char(1)) as AGECATEGORYID, --[char](1) NOT NULL, CAST(substring(ltrim(rtrim([PRIORITY])),1,1) as char(1)) as PRIORITY, --[char](1) NULL, CAST(substring(ltrim(rtrim([HIGHRISK])),1,1) as char(1)) as HIGHRISK, --[char](1) NOT NULL, substring(ltrim(rtrim([DESCRIPTION])),1,60) as DESCRIPTION, --[varchar](60) NULL, cast(substring(ltrim(rtrim([ASSIGNMENTMETHOD])),1,1) as char(1)) as ASSIGNMENTMETHOD, -- [char](1) NOT NULL, cast(substring(ltrim(rtrim([DFPPRIORITY])),1,1) as char(1)) as DFPPRIORITY, -- [char](1) NULL, cast(substring(ltrim(rtrim([FOODPACKAGEID])),1,10) as numeric (10,0)) as FOODPACKAGEID, -- [numeric](10, 0) NULL, ltrim(rtrim([DEFAULTASSESSMENT])) as DEFAULTASSESSMENT, -- [varchar](4000) NULL, ltrim(rtrim([DEFAULTPLAN])) as DEFAULTPLAN, -- [varchar](4000) NULL, [EFFECTIVEDATE], --[datetime] NULL, [EXPIRATIONDATE], --[datetime] NULL, [CREATEUSERID], --[varchar](20) NULL, [CREATEDTTM], --[datetime] NULL, [MODIFYUSERID], --[varchar](20) NULL, [MODIFYDTTM], --[datetime] NULL, cast(substring(ltrim(rtrim([THRESHOLDID])),1,18) as numeric (18,0)) as THRESHOLDID, -- [numeric](18, 0) NULL, cast(substring(ltrim(rtrim([AMOUNTBREASTFEEDING])),1,1) as char(1)) as AMOUNTBREASTFEEDING, -- [char](1) NOT NULL, cast(substring(ltrim(rtrim([ASSIGNMENTRESTRICTION])),1,1) as char(1)) as ASSIGNMENTRESTRICTION FROM [dbo].RISKFACTOR_REFERENCE_TEST I am using the data conversion transform in between staging and the destination tables: But the no of rows in the destination i am getting is less also the data which is picked is very different. ACtually while picking the RISKFACTORID from the excel my SSIS package is reading 421.2 as 421.199999 for putting that value i am using triming and cast i am doing these kind of things for several columns from staging. So what do you guys think where i am wrong? Thnks in advance Fighttillend_DBA/DEV

Use some development troubleshooting tips from here: http://www.bidn.com/blogs/DustinRyan/ssis/708/ssis-package-development-troubleshooting-tips Specifically enable the data watched to see what you get at each stage, I guess the metadata definition needs changes.Arthur My Blog

Arthurz by metadata deefinition you mean that the definition of the columns at the destination? Am i right? ThxFighttillend_DBA/DEV

CorrectArthur My Blog

If both databases are on same instance why are you using excel export and import onto second database. You can directly import data from staging to new database with SSIS or T-SQL scripts. Exporting onto Excel and importing into another database is not best practice as excel maimum rows allow would be ~65K also several data conversion problems raises just like one above.http://uk.linkedin.com/in/ramjaddu

Hi SQL_BOSS, 1) While the import from excel , try converting all the columns data type into text , basically in the excel source connection , go to Advanced properties and set all the output data types to DT_WSTR or DT_STR and load into the staging having all NVARCHAR or VARCHAR columns. This avoid the numeric columns not be misinterpreted. 2) Further , the columns can be converted as per the desired data type. Happy to help! Please mark the post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

somehow i manged to put some of the rows with ssis from staging to the destination but i am unable to put all as i am getting the following errors in SSIS: --[WSP_RISKFACTORREFERENCE [263]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E2F Description: "Violation of PRIMARY KEY constraint 'PK_RISKFACTORREFERENCE'. Cannot insert duplicate key in object 'dbo.RISKFACTORREFERENCE'.". --[WSP_RISKFACTORREFERENCE [263]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (276)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (276)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. I know this tells "Violation of PRIMARY KEY constraint 'PK_RISKFACTORREFERENCE'. Cannot insert duplicate key in object 'dbo.RISKFACTORREFERENCE'.". But how can i put all the data is there any way you guys can tell me.??? When i am using the insert into then i am getting same kind error but no row goes into the destination table SSIS atleast send some rows. So how i put all the data. Thaks Fighttillend_DBA/DEV

Arhurz that is the issue we cant change the metadat???? so what do you think we do . I have posted error just some minutes ago just update your thoughts please. ThxFighttillend_DBA/DEV

1) You could use SORT transformation to remove the duplicates in the source.Happy to help! Thanks. Regards and good Wishes, Deepak.

Two options a) you need to dedup the data is coming from with the existing the data b) you can use IGNORE_DUP_KEY option in the BOL (that couls slow down the insert)Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Deepak i can use the sort transform but i need those values users do not know technicality they want the data. So what should i do? ThanksFighttillend_DBA/DEV

1) Could not understand your question. 2)Do you want the duplicate data to be persistent or do you need some logging for the data? If so, you could log the errorneous rows using Error redirector in the data flow task.Happy to help! Thanks. Regards and good Wishes, Deepak.